package com.mzj.saas.mservice.community.service;

import cn.hutool.core.collection.CollectionUtil;
import com.mzj.saas.commons.ApiData;
import com.mzj.saas.commons.KeyWorker;
import com.mzj.saas.commons.ResultBean;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.mservice.area.entity.Area;
import com.mzj.saas.mservice.area.repository.AreaRepository;
import com.mzj.saas.mservice.community.entity.*;
import com.mzj.saas.mservice.community.repository.*;
import com.mzj.saas.mservice.community.vo.*;
import com.mzj.saas.mservice.department.entity.Department;
import com.mzj.saas.mservice.department.entity.UserDepartmentRef;
import com.mzj.saas.mservice.department.repository.DepartmentRepository;
import com.mzj.saas.mservice.department.repository.UserDepartmentRefRepository;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.house.vo.HouseVO;
import com.mzj.saas.mservice.house.vo.OrgVO;
import com.mzj.saas.mservice.lock.Repository.SmartLockRepository;
import com.mzj.saas.mservice.lock.entity.SmartLock;
import com.mzj.saas.mservice.lockManager.service.LockManagerService;
import com.mzj.saas.mservice.property.entity.CommunityHouseUser;
import com.mzj.saas.mservice.property.entity.EstateExpenseBill;
import com.mzj.saas.mservice.property.entity.EstateRentBill;
import com.mzj.saas.mservice.property.repository.CommunityHouseUserRepository;
import com.mzj.saas.mservice.property.repository.EstateExpenseBillRepository;
import com.mzj.saas.mservice.property.repository.EstateRentBillRepository;
import com.mzj.saas.mservice.propertybill.entity.HouseReturnPremium;
import com.mzj.saas.mservice.propertybill.repository.HouseReturnPremiumRepository;
import com.mzj.saas.mservice.propertybill.vo.HouseInfoVo;
import com.mzj.saas.mservice.sys.entity.User;
import com.mzj.saas.mservice.sys.repository.UserRepository;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

@Service
public class AppCommunityService {
    private static final Logger log = LoggerFactory.getLogger(AppCommunityService.class);

    @Autowired
    private RedisService redisService;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityBuildUnitRepository communityBuildUnitRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CommunityPublicFacilitiesRepository communityPublicFacilitiesRepository;
    @Autowired
    private CommunityBuildingOntologyRepository communityBuildingOntologyRepository;
    @Autowired
    private CommunitySafetyFacilitiesRepository communitySafetyFacilitiesRepository;
    @Autowired
    private CommunityServiceFacilitiesRepository communityServiceFacilitiesRepository;
    @Autowired
    private CommunityHouseUserRepository communityHouseUserRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private EstateExpenseBillRepository estateExpenseBillRepository;
    @Autowired
    private EstateRentBillRepository estateRentBillRepository;
    @Autowired
    @Qualifier("stringRedisTemplate")
    private StringRedisTemplate redisTemplate;
    @Autowired
    private AreaRepository areaRepository;
    @Autowired
    private CommunityService communityService;
    @Autowired
    private SmartLockRepository smartLockRepository;
    @Autowired
    private HouseTypeRepository houseTypeRepository;
    @Autowired
    private HouseReturnPremiumRepository houseReturnPremiumRepository;
    @Autowired
    private UserDepartmentRefRepository userDepartmentRefRepository;
    @Autowired
    private LockManagerService lockManagerService;
    /**
     * 查询小区列表
     *
     * @param accessToken
     * @param pageNumber  分页参数
     * @param pageSize    分页大小
     * @param vo
     * @param ids         小区id
     * @return 页面小区总数据
     * <p>
     * 查询登录用户所属房产小区详细信息，如有条件查询将条件查询的条件添加进查询
     */
    public ResultBean<ApiData<CommunityVO>> findCommunityList(String accessToken, Integer pageNumber, Integer pageSize, CommunityVO vo, List<String> ids) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }

            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            Long id = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

            //1、政府机构，根据区域标签所对应区域id查所有小区
            Long areaTagId = hierarchy.getTagId();//区域标签
            Long areaId = null;
            if (enterpriseTagIds.contains(3L)) {
                areaId = hierarchy.getAreaId();//区域id
            }
            String areaTag = "";
            if (areaTagId == 1) {
                areaTag = "province_id";
            } else if (areaTagId == 2) {
                areaTag = "city_id";
            } else if (areaTagId == 3) {
                areaTag = "district_id";
            } else if (areaTagId == 4) {
                areaTag = "street_id";
            } else if (areaTagId == 5) {
                areaTag = "community_id";
            }

            //2、产权单位，根据orgId查公租房所对应小区
            List<String> rcIds = new ArrayList<>();
            if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
                List<House> houses = houseRepository.findByOrgId(enterpriseId);
                rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
            }

            //3、物业，查已绑定该物业的小区
            /*Long hierarchyOrgId = null;
            if (enterpriseTagIds.contains(4L)) {
                hierarchyOrgId = hierarchy.getOrgId();
            }*/

            //当前登录用户自己所在企业以及下级企业是物业的
            List<Long> hierarchyOrgId = new ArrayList<>();
            List<OrgVO> org = propertyList(accessToken);
            hierarchyOrgId = org.stream().map(OrgVO::getOrgId).collect(Collectors.toList());

            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");
            where.append(" and (c." + areaTag + " = ? or c.org_id in  ");
            args.add(areaId);

            //拼接物业条件搜索
            if (hierarchyOrgId != null && !hierarchyOrgId.isEmpty()) {
                where.append(SqlUtils.foreachIn(hierarchyOrgId.size()));
                args.addAll(hierarchyOrgId);
            } else {
                where.append("(?)");
                args.add(null);
            }
            //拼接产权单位名下房屋所属小区
            where.append("  or c.id in  ");
            if (rcIds != null && !rcIds.isEmpty()) {
                where.append(SqlUtils.foreachIn(rcIds.size()));
                where.append(")");
                args.addAll(rcIds);
            } else {
                where.append("(?))");
                args.add(null);
            }

            where.append("  AND c.status = 1 ");

            //查询条件
            if (vo != null) {
                //小区ID
                if (StringUtils.isNotBlank(vo.getId())) {
                    where.append(" and c.id = ?");
                    args.add(vo.getId());
                }
                //传参查询
                if (ids != null && !ids.isEmpty()) {
                    where.append(" and c.id in ").append(SqlUtils.foreachIn(ids.size()));
                    args.addAll(ids);
                }
                //小区名称
                if (StringUtils.isNotBlank(vo.getName())) {
                    where.append(" and c.name like ?");
                    args.add("%" + vo.getName() + "%");
                }
                //楼宇id
                if (vo.getBuildId() != null) {
                    where.append(" and b.id = ?");
                    args.add(vo.getBuildId());
                }
                //单元id
                if (vo.getUnitId() != null) {
                    where.append(" and u.id = ?");
                    args.add(vo.getUnitId());
                }
                //房号
                if (StringUtils.isNotBlank(vo.getRoomNumber())) {
                    where.append(" and h.room_number = ?");
                    args.add(vo.getRoomNumber());
                }
                //楼层
                if (vo.getFloor() != null) {
                    where.append(" and h.floor = ?");
                    args.add(vo.getFloor());
                }
                //小区人口数(人)
                if (vo.getPopulationNumber() != null) {
                    where.append(" and c.population_number = ?");
                    args.add(vo.getPopulationNumber());
                }
                //联系电话
                if (StringUtils.isNotBlank(vo.getPhone())) {
                    where.append(" and c.phone = ?");
                    args.add(vo.getPhone());
                }
                //所属街道
                if (vo.getStreetId() != null) {
                    where.append(" and c.street_id = ?");
                    args.add(vo.getStreetId());
                }
                //所属社区
                if (vo.getCommunityId() != null) {
                    where.append(" and c.community_id = ?");
                    args.add(vo.getCommunityId());
                }
                //小区地址
                if (StringUtils.isNotBlank(vo.getAddress())) {
                    where.append(" and c.address like ?");
                    args.add("%" + vo.getAddress() + "%");
                }
                //物业企业id [小区物业]
                if (vo.getOrgId() != null) {
                    where.append(" and c.org_id = ?");
                    args.add(vo.getOrgId());
                }

            }

            String SQL = " SELECT c.*,   " +
                    "a1.name streetName,a2.name communityName, a3.name districtName,a4.name cityName ,a5.name provinceName    " +
                    "FROM saas_community c " +
                    "LEFT JOIN sys_area a1 ON c.street_id=a1.id " +
                    "LEFT JOIN sys_area a2 ON c.community_id=a2.id  " +
                    "LEFT JOIN sys_area a3 ON c.district_id=a3.id  " +
                    "LEFT JOIN sys_area a4 ON c.city_id=a4.id  " +
                    "LEFT JOIN sys_area a5 ON c.province_id=a5.id  ";

            String count = " SELECT COUNT(1)  " +
                    "FROM saas_community c " +
                    "LEFT JOIN sys_area a1 ON c.street_id=a1.id " +
                    "LEFT JOIN sys_area a2 ON c.community_id=a2.id  ";

            ApiData<CommunityVO> apiData = new ApiData<>();

            //统计登录用户房屋小区
            Long total = jdbcTemplate.queryForObject(count + where, Long.class, args.toArray());
            if (total == 0) {
                //无小区返回空
                return ResultBean.successfulResult(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY ").append(" c.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            //查询登录用户房屋小区.和所属街道层次
            List<CommunityVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(CommunityVO.class), args.toArray());

            //取出小区物业企业id
            List<Long> orgIdList = data.stream().map(CommunityVO::getOrgId).collect(Collectors.toList());
            List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByOrgIdIn(orgIdList);

            //小区id
            List<String> idList = data.stream().map(CommunityVO::getId).collect(Collectors.toList());
            //小区-市政设施
            List<CommunityPublicFacilities> communityPublicFacilitiesList = communityPublicFacilitiesRepository.findByCommunityIdIn(idList);
            List<CommunityPublicFacilitiesVO> communityPublicFacilitiesVOList = new ArrayList<>();
            communityPublicFacilitiesList.forEach(entity -> {
                CommunityPublicFacilitiesVO communityPublicFacilitiesVO = new CommunityPublicFacilitiesVO();
                BeanUtils.copyProperties(entity, communityPublicFacilitiesVO);
                communityPublicFacilitiesVOList.add(communityPublicFacilitiesVO);
            });
            //小区-房屋本体
            List<CommunityBuildingOntology> communityBuildingOntologyList = communityBuildingOntologyRepository.findByCommunityIdIn(idList);
            List<CommunityBuildingOntologyVO> communityBuildingOntologyVOList = new ArrayList<>();
            communityBuildingOntologyList.forEach(entity -> {
                CommunityBuildingOntologyVO communityBuildingOntologyVO = new CommunityBuildingOntologyVO();
                BeanUtils.copyProperties(entity, communityBuildingOntologyVO);
                communityBuildingOntologyVOList.add(communityBuildingOntologyVO);
            });
            //小区-公共服务设施
            List<CommunityServiceFacilities> communityServiceFacilitiesList = communityServiceFacilitiesRepository.findByCommunityIdIn(idList);
            List<CommunityServiceFacilitiesVO> communityServiceFacilitiesVOList = new ArrayList<>();
            communityServiceFacilitiesList.forEach(entity -> {
                CommunityServiceFacilitiesVO communityServiceFacilitiesVO = new CommunityServiceFacilitiesVO();
                BeanUtils.copyProperties(entity, communityServiceFacilitiesVO);
                communityServiceFacilitiesVOList.add(communityServiceFacilitiesVO);
            });
            //小区--安全设施
            List<CommunitySafetyFacilities> communitySafetyFacilitiesList = communitySafetyFacilitiesRepository.findByCommunityIdIn(idList);
            List<CommunitySafetyFacilitiesVO> communitySafetyFacilitiesVOList = new ArrayList<>();
            communitySafetyFacilitiesList.forEach(entity -> {
                CommunitySafetyFacilitiesVO communitySafetyFacilitiesVO = new CommunitySafetyFacilitiesVO();
                BeanUtils.copyProperties(entity, communitySafetyFacilitiesVO);
                communitySafetyFacilitiesVOList.add(communitySafetyFacilitiesVO);
            });

            //查出来对应的小区数据封装返回数据
            for (CommunityVO communityVO : data) {
                //小区-市政设施
                for (CommunityPublicFacilitiesVO communityPublicFacilitiesVO : communityPublicFacilitiesVOList) {
                    if (communityVO.getId().equals(communityPublicFacilitiesVO.getCommunityId())) {
                        communityVO.setCommunityPublicFacilitiesVO(communityPublicFacilitiesVO);
                    }
                }
                //小区-房屋本体
                for (CommunityBuildingOntologyVO communityBuildingOntologyVO : communityBuildingOntologyVOList) {
                    if (communityVO.getId().equals(communityBuildingOntologyVO.getCommunityId())) {
                        communityVO.setCommunityBuildingOntologyVO(communityBuildingOntologyVO);
                    }
                }
                //小区-公共服务设施
                for (CommunityServiceFacilitiesVO communityServiceFacilitiesVO : communityServiceFacilitiesVOList) {
                    if (communityVO.getId().equals(communityServiceFacilitiesVO.getCommunityId())) {
                        communityVO.setCommunityServiceFacilitiesVO(communityServiceFacilitiesVO);
                    }
                }
                //小区--安全设施
                for (CommunitySafetyFacilitiesVO communitySafetyFacilitiesVO : communitySafetyFacilitiesVOList) {
                    if (communityVO.getId().equals(communitySafetyFacilitiesVO.getCommunityId())) {
                        communityVO.setCommunitySafetyFacilitiesVO(communitySafetyFacilitiesVO);
                    }
                }

                //物业名称
                for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
                    if (communityVO.getOrgId() != null && communityVO.getOrgId().equals(departmentHierarchy.getOrgId())) {
                        communityVO.setOrgName(departmentHierarchy.getName());
                    }
                }

                int buildNum = communityBuildRepository.countByCommunityId(communityVO.getId());
                int unitNum = communityBuildUnitRepository.countByCommunityId(communityVO.getId());
                String sql = "SELECT count(1) from saas_house h WHERE h.residence_community_id = ? and housemate_id is null\n";
                Integer communityHouseNum = jdbcTemplate.queryForObject(sql, Integer.class,communityVO.getId());
              // int communityHouseNum = houseRepository.countByResidenceCommunityId(communityVO.getId());
                communityVO.setBuildNum(buildNum); //小区楼宇数
                communityVO.setUnitNum(unitNum);  //小区单元数
                communityVO.setCommunityHouseNum(communityHouseNum);  //小区房产数

            }

            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 添加/修改小区信息
     * @param accessToken
     * @param vo
     * 同一个区/县区域中，不能出现相同名称的小区，同一城市，不能出现相同的小区地址
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultBean<Object> saveCommunity(String accessToken, CommunityVO vo) {
        log.info(" AppCommunityService saveCommunity  accessToken={}, vo={} ", accessToken, vo.toString());

        try {
            //数据校验
            if (StringUtils.isBlank(vo.getName())) {
                return ResultBean.failedResultOfParamWithMsg("小区名称不能为空");
            }
            if (vo.getBuildTime() == null) {
                return ResultBean.failedResultOfParamWithMsg("建设时间不能为空");
            }
            if (vo.getLongitude() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区经度不能为空");
            }
            if (vo.getLatitude() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区纬度不能为空");
            }
      /*      if (StringUtils.isBlank(vo.getPhone())){
                return ResultBean.failedResultOfParamWithMsg("联系电话不能为空");
            }*/
            if (vo.getStreetId() == null) {
                return ResultBean.failedResultOfParamWithMsg("所属街道不能为空");
            }
            if (vo.getCommunityId() == null) {
                return ResultBean.failedResultOfParamWithMsg("所属社区不能为空");
            }
            if (StringUtils.isBlank(vo.getAddress())) {
                return ResultBean.failedResultOfParamWithMsg("小区地址不能为空");
            }
            if (vo.getCommunityTotalLandArea() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区总用地面积不能为空");
            }
            if (vo.getCommunityArea() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区占地面积不能为空");
            }
            if (vo.getInletOutletNumber() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区出入口数量不能为空");
            }
            if (vo.getPopulationNumber() == null) {
                return ResultBean.failedResultOfParamWithMsg("小区人口数不能为空");
            }
            if (vo.getIllegalBuilding() == null) {
                return ResultBean.failedResultOfParamWithMsg("违建不能为空");
            }
            if (vo.getBreachBuilding() == null) {
                return ResultBean.failedResultOfParamWithMsg("违搭不能为空");
            }
            if (vo.getOverheadPipeline() == null) {
                return ResultBean.failedResultOfParamWithMsg("架空管线不能为空");
            }
            if (vo.getWall() == null) {
                return ResultBean.failedResultOfParamWithMsg("围墙不能为空");
            }
            if (vo.getTagSystem() == null) {
                return ResultBean.failedResultOfParamWithMsg("标识系统不能为空");
            }
            if (StringUtils.isBlank(vo.getPropertyCompanyLimit())) {
                return ResultBean.failedResultOfParamWithMsg("物业公司管理范围不能为空");
            }
            if (vo.getOrgId() == null) {
                return ResultBean.failedResultOfParamWithMsg("物业企业不能为空");
            }

            //获取登录用户信息
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

            if (StringUtils.isBlank(vo.getId())) {
                //小区
                Community community = new Community();

                //同一个区/县区域中，不能出现相同名称的小区
                List<Community> entity = communityRepository.findByNameAndDistrictId(vo.getName(), vo.getDistrictId());
                if (entity.size() > 0) {
                    return ResultBean.failedResultOfParamWithMsg("同一个区/县区域中，不能出现相同名称的小区");
                }

                //相同城市不能出现校区地址相同
                List<Community> addressList = communityRepository.findByCityIdAndAddress(vo.getCityId(), vo.getAddress());
                if (addressList.size() > 0) {
                    return ResultBean.failedResultOfParamWithMsg("同一城市，不能出现相同的小区地址");
                }

                BeanUtils.copyProperties(vo, community);
                community.setStatus(1); //状态：1正常  4已删除
                community.setOperationId(tokenRedisVo.getId());
                community.setCreateTime(new Date());
                //使用时间戳雪花算法生成id
                community.setId("X" + KeyWorker.nextId());  //生成id

                Community save = communityRepository.save(community);


                //小区-市政设施
                CommunityPublicFacilities communityPublicFacilities = new CommunityPublicFacilities();

                BeanUtils.copyProperties(vo.getCommunityPublicFacilitiesVO(), communityPublicFacilities);

                communityPublicFacilities.setCommunityId(save.getId());

                communityPublicFacilitiesRepository.save(communityPublicFacilities);


                //小区--安全设施
                CommunitySafetyFacilities communitySafetyFacilities = new CommunitySafetyFacilities();

                BeanUtils.copyProperties(vo.getCommunitySafetyFacilitiesVO(), communitySafetyFacilities);

                communitySafetyFacilities.setCommunityId(save.getId());

                communitySafetyFacilitiesRepository.save(communitySafetyFacilities);


                //小区-公共服务设施
                CommunityServiceFacilities communityServiceFacilities = new CommunityServiceFacilities();

                BeanUtils.copyProperties(vo.getCommunityServiceFacilitiesVO(), communityServiceFacilities);

                communityServiceFacilities.setCommunityId(save.getId());

                communityServiceFacilitiesRepository.save(communityServiceFacilities);


                //小区-房屋本体
                CommunityBuildingOntology communityBuildingOntology = new CommunityBuildingOntology();

                BeanUtils.copyProperties(vo.getCommunityBuildingOntologyVO(), communityBuildingOntology);

                communityBuildingOntology.setCommunityId(save.getId());

                communityBuildingOntologyRepository.save(communityBuildingOntology);

            } else {     //更新修改

                Community community = new Community();
                Optional<Community> communityOptional = communityRepository.findById(vo.getId());
                if (!communityOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                BeanUtils.copyProperties(communityOptional.get(), community);

                //同一个区/县区域中，不能出现相同名称的小区
                if ((!community.getName().equals(vo.getName())) || (!community.getDistrictId().equals(vo.getDistrictId()))) {
                    List<Community> entity = communityRepository.findByNameAndDistrictId(vo.getName(), vo.getDistrictId());
                    if (entity.size() > 0) {
                        return ResultBean.failedResultOfParamWithMsg("同一个区/县区域中，不能出现相同名称的小区");
                    }
                }

                if ((!community.getAddress().equals(vo.getAddress())) || (!community.getCityId().equals(vo.getCityId()))) {
                    List<Community> addressList = communityRepository.findByCityIdAndAddress(vo.getCityId(), vo.getAddress());
                    if (addressList.size() > 0) {
                        return ResultBean.failedResultOfParamWithMsg("同一城市，不能出现相同的小区地址");
                    }
                }

                //封装小区基本信息
                community.setName(vo.getName());
                community.setBuildTime(vo.getBuildTime());
                community.setPhone(vo.getPhone());
                community.setStreetId(vo.getStreetId());
                community.setCommunityId(vo.getCommunityId());
                community.setAddress(vo.getAddress());

                community.setProvinceId(vo.getProvinceId());
                community.setCityId(vo.getCityId());
                community.setDistrictId(vo.getDistrictId());

                community.setCommunityTotalLandArea(vo.getCommunityTotalLandArea());
                community.setCommunityArea(vo.getCommunityArea());
                community.setInletOutletNumber(vo.getInletOutletNumber());
                community.setPopulationNumber(vo.getPopulationNumber());
                community.setIllegalBuilding(vo.getIllegalBuilding());
                community.setBreachBuilding(vo.getBreachBuilding());
                community.setOverheadPipeline(vo.getOverheadPipeline());
                community.setWall(vo.getWall());
                community.setTagSystem(vo.getTagSystem());
                community.setGreenArea(vo.getGreenArea());
                community.setGreenRate(vo.getGreenRate());
                community.setGate(vo.getGate());

                community.setPropertyCompany(vo.getPropertyCompany());
                community.setOwnerCommittee(vo.getOwnerCommittee());
                community.setPropertyChargingStandard(vo.getPropertyChargingStandard());
                community.setPropertyChargeRate(vo.getPropertyChargeRate());
                community.setPropertyCompanyName(vo.getPropertyCompanyName());
                community.setPropertyCompanyLimit(vo.getPropertyCompanyLimit());
                community.setOrgId(vo.getOrgId());
                community.setLongitude(vo.getLongitude());
                community.setLatitude(vo.getLatitude());

                communityRepository.save(community);

                //小区-市政设施
                CommunityPublicFacilities communityPublicFacilities = new CommunityPublicFacilities();

                CommunityPublicFacilitiesVO communityPublicFacilitiesVO = vo.getCommunityPublicFacilitiesVO();
                Optional<CommunityPublicFacilities> communityPublicFacilitiesOptional = communityPublicFacilitiesRepository.findById(communityPublicFacilitiesVO.getId());
                //校验市政设施是否存在
                if (!communityPublicFacilitiesOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                BeanUtils.copyProperties(communityPublicFacilitiesOptional.get(), communityPublicFacilities);

                //市政设施数据封装
                communityPublicFacilities.setLaneWidth(communityPublicFacilitiesVO.getLaneWidth());
                communityPublicFacilities.setMotorVehicles(communityPublicFacilitiesVO.getMotorVehicles());
                communityPublicFacilities.setNonMotorVehicles(communityPublicFacilitiesVO.getNonMotorVehicles());
                communityPublicFacilities.setPrivateWalkway(communityPublicFacilitiesVO.getPrivateWalkway());
                communityPublicFacilities.setBikeway(communityPublicFacilitiesVO.getBikeway());
                communityPublicFacilities.setTrafficSigns(communityPublicFacilitiesVO.getTrafficSigns());
                communityPublicFacilities.setFitnessTrails(communityPublicFacilitiesVO.getFitnessTrails());
                communityPublicFacilities.setMainRoadMaterial(communityPublicFacilitiesVO.getMainRoadMaterial());

                communityPublicFacilities.setMainWaterSupply(communityPublicFacilitiesVO.getMainWaterSupply());
                communityPublicFacilities.setModificationTime(communityPublicFacilitiesVO.getModificationTime());
                communityPublicFacilities.setPipeWaterSupply(communityPublicFacilitiesVO.getPipeWaterSupply());
                communityPublicFacilities.setPowerSupplyLine(communityPublicFacilitiesVO.getPowerSupplyLine());
                communityPublicFacilities.setPowerSupplyBranch(communityPublicFacilitiesVO.getPowerSupplyBranch());
                communityPublicFacilities.setHeatingMainLine(communityPublicFacilitiesVO.getHeatingMainLine());
                communityPublicFacilities.setHeatingFeeder(communityPublicFacilitiesVO.getHeatingFeeder());

                communityPublicFacilities.setGasLines(communityPublicFacilitiesVO.getGasLines());
                communityPublicFacilities.setGasFeeder(communityPublicFacilitiesVO.getGasFeeder());
                communityPublicFacilities.setMainDrainage(communityPublicFacilitiesVO.getMainDrainage());
                communityPublicFacilities.setBranchDrainage(communityPublicFacilitiesVO.getBranchDrainage());
                communityPublicFacilities.setStreet(communityPublicFacilitiesVO.getStreet());
                communityPublicFacilities.setAnnouncementLandscapeLighting(communityPublicFacilitiesVO.getAnnouncementLandscapeLighting());
                communityPublicFacilities.setRefuseCollectionFacility(communityPublicFacilitiesVO.getRefuseCollectionFacility());
                communityPublicFacilities.setGarbageSorting(communityPublicFacilitiesVO.getGarbageSorting());
                communityPublicFacilities.setScrapPoint(communityPublicFacilitiesVO.getScrapPoint());
                communityPublicFacilities.setPublicToilet(communityPublicFacilitiesVO.getPublicToilet());
                communityPublicFacilities.setCommunicationStation(communityPublicFacilitiesVO.getCommunicationStation());
                communityPublicFacilities.setCommunicationChannel(communityPublicFacilitiesVO.getCommunicationChannel());

                communityPublicFacilities.setCommunicationFacilities(communityPublicFacilitiesVO.getCommunicationFacilities());
                communityPublicFacilities.setOpticalFiber(communityPublicFacilitiesVO.getOpticalFiber());

                communityPublicFacilities.setPipeWaterSupplyTime(communityPublicFacilitiesVO.getPipeWaterSupplyTime());
                communityPublicFacilities.setPowerSupplyLineTime(communityPublicFacilitiesVO.getPowerSupplyLineTime());
                communityPublicFacilities.setPowerSupplyBranchTime(communityPublicFacilitiesVO.getPowerSupplyBranchTime());
                communityPublicFacilities.setHeatingMainLineTime(communityPublicFacilitiesVO.getHeatingMainLineTime());
                communityPublicFacilities.setHeatingFeederTime(communityPublicFacilitiesVO.getHeatingFeederTime());
                communityPublicFacilities.setGasLinesTime(communityPublicFacilitiesVO.getGasLinesTime());
                communityPublicFacilities.setGasFeederTime(communityPublicFacilitiesVO.getGasFeederTime());
                communityPublicFacilities.setMainDrainageTime(communityPublicFacilitiesVO.getMainDrainageTime());
                communityPublicFacilities.setBranchDrainageTime(communityPublicFacilitiesVO.getBranchDrainageTime());

                communityPublicFacilitiesRepository.save(communityPublicFacilities);

                //小区--安全设施
                CommunitySafetyFacilities communitySafetyFacilities = new CommunitySafetyFacilities();

                CommunitySafetyFacilitiesVO communitySafetyFacilitiesVO = vo.getCommunitySafetyFacilitiesVO();
                Optional<CommunitySafetyFacilities> communitySafetyFacilitiesOptional = communitySafetyFacilitiesRepository.findById(communitySafetyFacilitiesVO.getId());
                //校验安全设施是否存在
                if (!communitySafetyFacilitiesOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                BeanUtils.copyProperties(communitySafetyFacilitiesOptional.get(), communitySafetyFacilities);

                //小区--安全设施数据封装
                communitySafetyFacilities.setFireEngineAccess(communitySafetyFacilitiesVO.getFireEngineAccess());
                communitySafetyFacilities.setFireFightingDevice(communitySafetyFacilitiesVO.getFireFightingDevice());
                communitySafetyFacilities.setDoorSystem(communitySafetyFacilitiesVO.getDoorSystem());
                communitySafetyFacilities.setSucurityMonitorSystem(communitySafetyFacilitiesVO.getSucurityMonitorSystem());

                communitySafetyFacilities.setThunderFacilities(communitySafetyFacilitiesVO.getThunderFacilities());
                communitySafetyFacilities.setEmergencyShelter(communitySafetyFacilitiesVO.getEmergencyShelter());
                communitySafetyFacilities.setEmergencyShelterBuilding(communitySafetyFacilitiesVO.getEmergencyShelterBuilding());

                communitySafetyFacilitiesRepository.save(communitySafetyFacilities);

                //小区-公共服务设施
                CommunityServiceFacilities communityServiceFacilities = new CommunityServiceFacilities();

                CommunityServiceFacilitiesVO communityServiceFacilitiesVO = vo.getCommunityServiceFacilitiesVO();
                Optional<CommunityServiceFacilities> communityServiceFacilitiesOptional = communityServiceFacilitiesRepository.findById(communityServiceFacilitiesVO.getId());
                //校验公共服务设施是否存在
                if (!communityServiceFacilitiesOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                BeanUtils.copyProperties(communityServiceFacilitiesOptional.get(), communityServiceFacilities);

                //小区-公共服务设施数据封装
                communityServiceFacilities.setOutdoorPlaza(communityServiceFacilitiesVO.getOutdoorPlaza());
                communityServiceFacilities.setOutdoorFitnessFacilities(communityServiceFacilitiesVO.getOutdoorFitnessFacilities());
                communityServiceFacilities.setChildActivityFacilities(communityServiceFacilitiesVO.getChildActivityFacilities());
                communityServiceFacilities.setOutdoorRestingFacilities(communityServiceFacilitiesVO.getOutdoorRestingFacilities());

                communityServiceFacilities.setBulletinBoard(communityServiceFacilitiesVO.getBulletinBoard());
                communityServiceFacilities.setEntranceWheelchairRamp(communityServiceFacilitiesVO.getEntranceWheelchairRamp());
                communityServiceFacilities.setOutdoorWheelchairRamp(communityServiceFacilitiesVO.getOutdoorWheelchairRamp());
                communityServiceFacilities.setCommunityServices(communityServiceFacilitiesVO.getCommunityServices());

                communityServiceFacilities.setNewCabinet(communityServiceFacilitiesVO.getNewCabinet());
                communityServiceFacilities.setPropertyManagementOccupancy(communityServiceFacilitiesVO.getPropertyManagementOccupancy());
                communityServiceFacilities.setExpressDeliveryFacilities(communityServiceFacilitiesVO.getExpressDeliveryFacilities());
                communityServiceFacilities.setElderlyService(communityServiceFacilitiesVO.getElderlyService());

                communityServiceFacilities.setChargingPile(communityServiceFacilitiesVO.getChargingPile());
                communityServiceFacilities.setChargingPileNumber(communityServiceFacilitiesVO.getChargingPileNumber());

                communityServiceFacilitiesRepository.save(communityServiceFacilities);

                //小区-房屋本体
                CommunityBuildingOntology communityBuildingOntology = new CommunityBuildingOntology();

                CommunityBuildingOntologyVO communityBuildingOntologyVO = vo.getCommunityBuildingOntologyVO();
                Optional<CommunityBuildingOntology> communityBuildingOntologyOptional = communityBuildingOntologyRepository.findById(communityBuildingOntologyVO.getId());
                //校验房屋本体数据是否存在
                if (!communityBuildingOntologyOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                BeanUtils.copyProperties(communityBuildingOntologyOptional.get(), communityBuildingOntology);

                //小区-房屋本体数据封装
                communityBuildingOntology.setWallTemperatureRemould(communityBuildingOntologyVO.getWallTemperatureRemould());
                communityBuildingOntology.setModificationTime(communityBuildingOntologyVO.getModificationTime());
                communityBuildingOntology.setRenovationBuildingNumber(communityBuildingOntologyVO.getRenovationBuildingNumber());
                communityBuildingOntology.setReconstructionFacade(communityBuildingOntologyVO.getReconstructionFacade());

                communityBuildingOntology.setCorridorPipelineRenovation(communityBuildingOntologyVO.getCorridorPipelineRenovation());
                communityBuildingOntology.setOutdoorWaterElectricity(communityBuildingOntologyVO.getOutdoorWaterElectricity());
                communityBuildingOntology.setCorridorLightingRenovation(communityBuildingOntologyVO.getCorridorLightingRenovation());
                communityBuildingOntology.setBuildingScatteredWater(communityBuildingOntologyVO.getBuildingScatteredWater());

                communityBuildingOntology.setConrridorDoorWindows(communityBuildingOntologyVO.getConrridorDoorWindows());
                communityBuildingOntology.setWhetherElevatorInstalled(communityBuildingOntologyVO.getWhetherElevatorInstalled());
                communityBuildingOntology.setSeismicReinforcementStairs(communityBuildingOntologyVO.getSeismicReinforcementStairs());

                communityBuildingOntology.setReconstructionFacadeTime(communityBuildingOntologyVO.getReconstructionFacadeTime());
                communityBuildingOntology.setReconstructionFacadeNumber(communityBuildingOntologyVO.getReconstructionFacadeNumber());
                communityBuildingOntology.setPipelineRenovationTime(communityBuildingOntologyVO.getPipelineRenovationTime());
                communityBuildingOntology.setPipelineRenovationNumber(communityBuildingOntologyVO.getPipelineRenovationNumber());
                communityBuildingOntology.setWaterElectricityTime(communityBuildingOntologyVO.getWaterElectricityTime());
                communityBuildingOntology.setWaterElectricityNumber(communityBuildingOntologyVO.getWaterElectricityNumber());
                communityBuildingOntology.setLightingRenovationTime(communityBuildingOntologyVO.getLightingRenovationTime());
                communityBuildingOntology.setLightingRenovationNumber(communityBuildingOntologyVO.getLightingRenovationNumber());
                communityBuildingOntology.setScatteredWaterTime(communityBuildingOntologyVO.getScatteredWaterTime());
                communityBuildingOntology.setScatteredWaterNumber(communityBuildingOntologyVO.getScatteredWaterNumber());
                communityBuildingOntology.setDoorWindowsTime(communityBuildingOntologyVO.getDoorWindowsTime());
                communityBuildingOntology.setDoorWindowsNumber(communityBuildingOntologyVO.getDoorWindowsNumber());

                communityBuildingOntologyRepository.save(communityBuildingOntology);

                //获取小区房产
                List<House> communityHouseList = houseRepository.findByResidenceCommunityId(community.getId());
                for (House h : communityHouseList) {
                    h.setAddress(community.getAddress());
                }
                houseRepository.saveAll(communityHouseList);

            }

            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException();
        }
    }

    /**
     * 删除小区
     * 小区下有楼宇无法删除
     */
    @Transactional
    public ResultBean<Object> deleteCommunity(String accessToken, String id) {
        log.info("AppCommunityService deleteCommunity accessToken={},id={}", accessToken, id);

        Optional<Community> optional = communityRepository.findById(id);
        //数据校验
        if (!optional.isPresent()) {
            return ResultBean.failedResultWithMsg("当前小区数据不存在！");
        }

        List<CommunityBuild> communityBuildList = communityBuildRepository.findByCommunityId(id);
        if (communityBuildList.size() > 0) {
            return ResultBean.failedResultWithMsg("当前小区下有楼宇,无法删除");
        }

        communityRepository.deleteById(id);
        //相关联数据

        //小区--市政设施
        communityPublicFacilitiesRepository.deleteByCommunityId(id);
        //小区--安全设施
        communityBuildingOntologyRepository.deleteByCommunityId(id);
        //小区--公共服务设施
        communityServiceFacilitiesRepository.deleteByCommunityId(id);
        //小区--房屋本体
        communitySafetyFacilitiesRepository.deleteByCommunityId(id);

        return ResultBean.successfulResult(null);
    }

    /**
     * 楼宇列表
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param vo
     * @return 查询本小区所有楼宇
     */
    public ResultBean<ApiData<CommunityBuildVO>> findCommunityBuildList(String accessToken, Integer pageNumber, Integer pageSize, CommunityBuildVO vo) {

        try {

            if (StringUtils.isBlank(vo.getCommunityId())) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            String SQL = " SELECT b.* FROM saas_community_build b  ";

            String count = "  SELECT count(1) FROM saas_community_build b  ";

            StringBuilder where = new StringBuilder("WHERE 1 = 1 \n");
            List<Object> args = new ArrayList<>();

            where.append(" AND b.community_id = ? \n");
            args.add(vo.getCommunityId());

            //模糊查询楼宇名称
            if (vo != null) {
                if (StringUtils.isNotBlank(vo.getName())) {
                    where.append(" AND b.name like ? \n");
                    args.add("%" + vo.getName() + "%");
                }

            }
            //查询楼宇数量
            ApiData<CommunityBuildVO> apiData = new ApiData<>();
            Long total = jdbcTemplate.queryForObject(count + where, Long.class, args.toArray());
            if (total == 0) {
                //数量为0返回空
                return ResultBean.successfulResult(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY ").append(" b.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            //查询小区与楼宇
            List<CommunityBuildVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(CommunityBuildVO.class), args.toArray());

            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);

        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 添加/修改
     *
     * @param accessToken
     * @param vo
     * 楼宇判断当前是否存在楼宇存在即返回已存在，不存在即新增楼宇
     */
    @Transactional
    public ResultBean<Object> saveCommunityBuild(String accessToken, CommunityBuildVO vo) {
        log.info(" AppCommunityService saveCommunityBuild accessToken={},vo={} ", accessToken, vo.toString());

        if (StringUtils.isBlank(vo.getName())) {
            return ResultBean.failedResultOfParamWithMsg("楼宇名称不能为空");
        }
        if (vo.getHouseNumber() == null) {
            return ResultBean.failedResultOfParamWithMsg("层数不能为空");
        }

        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

        CommunityBuild communityBuild = new CommunityBuild();
        if (vo.getId() == null) {//新增
            //判断小区内是否有相同的楼宇
            List<CommunityBuild> list = communityBuildRepository.findByCommunityIdAndName(vo.getCommunityId(), vo.getName());
            if (list.size() > 0) {
                return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现同一个楼宇");
            }


            communityBuild.setCommunityId(vo.getCommunityId());
            communityBuild.setOperationId(tokenRedisVo.getId());
            communityBuild.setCreateTime(new Date());

        } else {
            //查询id是否存在数据
            Optional<CommunityBuild> communityBuildOptional = communityBuildRepository.findById(vo.getId());
            if (!communityBuildOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("当前数据已不存在！");
            }
            //根据vo的id查询对应是否存在相同楼宇
            if ((!communityBuildOptional.get().getName().equals(vo.getName())) || (!communityBuildOptional.get().getCommunityId().equals(vo.getCommunityId()))) {
                List<CommunityBuild> list = communityBuildRepository.findByCommunityIdAndName(vo.getCommunityId(), vo.getName());
                if (list.size() > 0) {
                    return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现同一个楼宇");
                }
            }
            //不同就数据复制
            BeanUtils.copyProperties(communityBuildOptional.get(), communityBuild);
        }

        communityBuild.setName(vo.getName());
        communityBuild.setType(vo.getType());
        communityBuild.setHouseNumber(vo.getHouseNumber());

        communityBuildRepository.save(communityBuild);

        return ResultBean.successfulResult(null);
    }

    /**
     * 删除楼宇
     *
     * @param accessToken
     * @param id
     * @return 查询楼宇下是否有单元有单元无法删除
     */
    @Transactional
    public ResultBean<Object> deleteCommunityBuild(String accessToken, Long id) {
        log.info("AppCommunityService deleteCommunityBuild accessToken={},id={}", accessToken, id);

        Optional<CommunityBuild> optional = communityBuildRepository.findById(id);
        if (!optional.isPresent()) {
            return ResultBean.failedResultWithMsg("当前楼宇数据不存在！");
        }
        //查询楼宇下是否有单元有单元无法删除
        List<CommunityBuildUnit> communityBuildUnitList = communityBuildUnitRepository.findByBuildId(id);

        if (communityBuildUnitList.size() > 0) {
            return ResultBean.failedResultWithMsg("当前楼宇下有单元,无法删除");
        }

        communityBuildRepository.deleteById(id);

        return ResultBean.successfulResult(null);
    }

    /**
     * 查询单元
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param vo
     * @return 查询楼宇所有单元
     */
    public ResultBean<ApiData<CommunityBuildUnitVO>> findCommunityBuildUnitList(String accessToken, Integer pageNumber, Integer pageSize, CommunityBuildUnitVO vo) {
        log.info("AppCommunityService findCommunityBuildUnitList accessToken={},id={}", accessToken, vo.toString());

        try {
            //小区判空
            if (StringUtils.isBlank(vo.getCommunityId())) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
         /*   if(vo.getBuildId() == null){
                return ResultBean.failedResultOfParamWithMsg("楼宇id为空");
            }*/

            String SQL = " SELECT u.*,b.name buildName  FROM saas_community_build_unit u  LEFT JOIN saas_community_build b ON  u.build_id=b.id  ";

            String count = "  SELECT count(1) FROM saas_community_build_unit u LEFT JOIN saas_community_build b ON  u.build_id=b.id   ";

            StringBuilder where = new StringBuilder("WHERE 1 = 1 \n");
            List<Object> args = new ArrayList<>();

            where.append(" AND u.community_id = ? \n");
            args.add(vo.getCommunityId());

      /*      where.append(" AND u.build_id = ? \n");
            args.add(vo.getBuildId());*/

            //模糊查询单元
            if (vo != null) {
                if (StringUtils.isNotBlank(vo.getName())) {
                    where.append(" AND u.name like ? \n");
                    args.add("%" + vo.getName() + "%");
                }
            }

            //查询是否有单元
            ApiData<CommunityBuildUnitVO> apiData = new ApiData<>();
            Long total = jdbcTemplate.queryForObject(count + where, Long.class, args.toArray());
            if (total == 0) {

                return ResultBean.successfulResult(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY ").append(" u.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            //查询楼宇与与单元
            List<CommunityBuildUnitVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(CommunityBuildUnitVO.class), args.toArray());

            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);

        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 新增单元
     *
     * @param accessToken
     * @param voList      一个或多个单元
     * @return 添加一个或多个单元、同一个楼宇下不能有重复单元
     */
    @Transactional
    public ResultBean<Object> addCommunityBuildUnit(String accessToken, List<CommunityBuildUnitVO> voList) {
        log.info(" AppCommunityService addCommunityBuildUnit accessToken={},vo={} ", accessToken, voList.toString());

        for (CommunityBuildUnitVO vo : voList) {

            if (StringUtils.isBlank(vo.getName())) {
                return ResultBean.failedResultOfParamWithMsg("单元名称不能为空");
            }
            if (vo.getBuildId() == null) {
                return ResultBean.failedResultOfParamWithMsg("楼宇不能为空");
            }
            if (StringUtils.isBlank(vo.getCommunityId())) {
                return ResultBean.failedResultOfParamWithMsg("小区id不能为空");
            }

            //查询本楼宇是否有同一个单元
            List<CommunityBuildUnit> list = communityBuildUnitRepository.findByBuildIdAndName(vo.getBuildId(), vo.getName());
            if (list.size() > 0) {
                //重复添加
                return ResultBean.failedResultOfParamWithMsg("同一个楼宇,单元不能重复");
            }

            //获取当前操作人
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

            CommunityBuildUnit communityBuildUnit = new CommunityBuildUnit();

            communityBuildUnit.setCommunityId(vo.getCommunityId());
            communityBuildUnit.setBuildId(vo.getBuildId());
            communityBuildUnit.setOperationId(tokenRedisVo.getId());
            communityBuildUnit.setCreateTime(new Date());
            communityBuildUnit.setName(vo.getName());

            communityBuildUnitRepository.save(communityBuildUnit);

        }

        return ResultBean.successfulResult(null);
    }

    /**
     * 修改单元
     *
     * @param accessToken
     * @param voList
     * @return 修改单元判断当前是否存在楼宇存在即修改，不存在即新增单元
     */
    @Transactional
    public ResultBean<Object> updatCommunityBuildUnit(String accessToken, List<CommunityBuildUnitVO> voList) {
        log.info(" AppCommunityService updatCommunityBuildUnit accessToken={},vo={} ", accessToken, voList.toString());
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

        for (CommunityBuildUnitVO vo : voList) {
            if (StringUtils.isBlank(vo.getName())) {
                return ResultBean.failedResultOfParamWithMsg("单元名称不能为空");
            }
            if (vo.getBuildId() == null) {
                return ResultBean.failedResultOfParamWithMsg("楼宇不能为空");
            }
            if (StringUtils.isBlank(vo.getCommunityId())) {
                return ResultBean.failedResultOfParamWithMsg("小区id不能为空");
            }

            CommunityBuildUnit communityBuildUnit = new CommunityBuildUnit();
            //未传id封装数据
            if (vo.getId() == null) {
                communityBuildUnit.setCommunityId(vo.getCommunityId());
                communityBuildUnit.setOperationId(tokenRedisVo.getId());
                communityBuildUnit.setCreateTime(new Date());

            } else {
                //判断当前id是否有数据
                Optional<CommunityBuildUnit> communityBuildUnitOptional = communityBuildUnitRepository.findById(vo.getId());
                if (!communityBuildUnitOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("当前数据已不存在！");
                }
                //判重
                if ((!communityBuildUnitOptional.get().getName().equals(vo.getName())) || (!communityBuildUnitOptional.get().getBuildId().equals(vo.getBuildId()))) {
                    List<CommunityBuildUnit> list = communityBuildUnitRepository.findByBuildIdAndName(vo.getBuildId(), vo.getName());
                    if (list.size() > 0) {
                        return ResultBean.failedResultOfParamWithMsg("同一个楼宇,单元不能重复");
                    }
                }
                //不同则复制数据
                BeanUtils.copyProperties(communityBuildUnitOptional.get(), communityBuildUnit);
            }

            communityBuildUnit.setName(vo.getName());
            communityBuildUnit.setBuildId(vo.getBuildId());

            communityBuildUnitRepository.save(communityBuildUnit);
        }

        return ResultBean.successfulResult(null);
    }

    /**
     * 删除单元
     *
     * @param accessToken
     * @param id
     * @return 查询楼宇下是否有房产有房产无法删除
     */
    @Transactional
    public ResultBean<Object> deleteCommunityBuildUnit(String accessToken, Long id) {
        log.info("AppCommunityService deleteCommunityBuildUnit accessToken={},id={}", accessToken, id);

        Optional<CommunityBuildUnit> optional = communityBuildUnitRepository.findById(id);
        if (!optional.isPresent()) {
            return ResultBean.failedResultWithMsg("当前单元数据不存在！");
        }

        String sql = "select count(0) from  saas_house where status=1 and unit=?";
        Long aLong = jdbcTemplate.queryForObject(sql, Long.class, id);
        if (aLong > 0) {
            return ResultBean.failedResultWithMsg("当前单元下有房产,无法删除");
        }

        communityBuildUnitRepository.deleteById(id);

        return ResultBean.successfulResult(null);
    }

    /**
     * 物业房产列表
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param vo
     * @return 查询当前小区房产列表
     */
    public ResultBean<ApiData<HouseVO>> findCommunityHouseList(String accessToken, Integer pageNumber, Integer pageSize, HouseVO vo) {
        log.info("AppCommunityService findCommunityHouseList accessToken={},id={}", accessToken, vo.toString());

        try {
            if (StringUtils.isBlank(vo.getResidenceCommunityId())) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            String SQL = " SELECT h.*,b.name buildingNumName,u.name unitName," +
                    "           a1.name province,a2.name city,a3.name district,a4.name street,a5.name community   " +
                    "          FROM  saas_house h " +
                    "          LEFT JOIN saas_community_build b ON b.id=h.building_num  " +
                    "          LEFT JOIN saas_community_build_unit u ON h.unit=u.id  " +
                    "          LEFT JOIN sys_area a1 ON h.province_id=a1.id  " +
                    "          LEFT JOIN sys_area a2 ON h.city_id=a2.id  " +
                    "          LEFT JOIN sys_area a3 ON h.district_id=a3.id  " +
                    "          LEFT JOIN sys_area a4 ON h.street_id=a4.id  " +
                    "          LEFT JOIN sys_area a5 ON h.community_id=a5.id    ";

            String count = " SELECT count(1) FROM  saas_house h LEFT JOIN saas_community_build_unit u ON h.unit=u.id  ";

            StringBuilder where = new StringBuilder("WHERE 1 = 1 AND h.housemate_id is null \n");
            List<Object> args = new ArrayList<>();

            where.append(" AND h.residence_community_id = ? \n");
            args.add(vo.getResidenceCommunityId());

            //模糊查询房号
            if (vo != null) {
                if (StringUtils.isNotBlank(vo.getRoomNumber())) {
                    where.append(" AND h.room_number like ? \n");
                    args.add("%" + vo.getRoomNumber() + "%");
                }
            }

            //判断当前是否存在房产
            ApiData<HouseVO> apiData = new ApiData<>();
            Long total = jdbcTemplate.queryForObject(count + where , Long.class, args.toArray());
            if (total == 0) {
                return ResultBean.successfulResult(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY ").append(" h.create_time desc ").append(" \n").append(offsets);
            args.add(pageSize);
            args.add(pageNumber * pageSize);
            //查询单元名称、楼宇名称、房产、街道层级
            List<HouseVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());

            //查询物业名称
            List<Long> housePropertyIdList = data.stream().map(HouseVO::getHousePropertyId).collect(Collectors.toList());
            List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByOrgIdIn(housePropertyIdList);
            //查询出房屋属性；
            Iterable<HouseType> all = houseTypeRepository.findAll();
            //for (HouseVO houseVO : data) {
            //    for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
            //        if (houseVO.getHousePropertyId().equals(departmentHierarchy.getOrgId())) {
            //            //设置对应物业公司名
            //            houseVO.setHouseProperty(departmentHierarchy.getName());
            //        }
            //    }
            ////    新增房屋性质 20230307 xiaolong
            //    Integer houseNature = houseVO.getHouseNature();
            //    for (HouseType houseType : all) {
            //        Integer id = Math.toIntExact(houseType.getId());
            //        if (houseNature == id) {
            //            houseVO.setHouseNatureName(houseType.getType());
            //        }
            //    }
            //}

            //查询小区名称
            List<String> rcIds = data.stream().filter(h -> h.getResidenceCommunityId() != null).map(HouseVO::getResidenceCommunityId).distinct().collect(Collectors.toList());
            if (!rcIds.isEmpty()) {
                Map<String, String> map = communityRepository.findByIdIn(rcIds).stream().collect(Collectors.toMap(Community::getId, Community::getName));
                if (!map.isEmpty()) {
                    //设置对应小区名
                    data.forEach(h -> h.setResidenceCommunityName(map.get(vo.getResidenceCommunityId())));
                }
            }

            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);

        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 新增物业房屋
     *
     * @param accessToken
     * @param vo
     * @return
     */
    @Transactional
    public ResultBean<Object> saveCommunityHouse(String accessToken, HouseVO vo) {
        log.info("AppCommunityService saveCommunityHouse accessToken={},id={}", accessToken, vo.toString());
        //判断当前登录信息
        TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
        if (tokenVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //数据校验
        if (vo.getProvinceId() == null) {
            return ResultBean.failedResultOfParamWithMsg("区域为空");
        }
        if (StringUtils.isBlank(vo.getAddress())) {
            return ResultBean.failedResultOfParamWithMsg("地址为空");
        }
        if (StringUtils.isBlank(vo.getResidenceCommunityId())) {
            return ResultBean.failedResultOfParamWithMsg("小区名称为空");
        }
        if (vo.getBuildingNum() == null) {
            return ResultBean.failedResultOfParamWithMsg("楼栋为空");
        }
        if (vo.getUnit() == null) {
            return ResultBean.failedResultOfParamWithMsg("单元为空");
        }
        if (StringUtils.isBlank(vo.getRoomNumber())) {
            return ResultBean.failedResultOfParamWithMsg("房号为空");
        }
        if (vo.getFloor() == null) {
            return ResultBean.failedResultOfParamWithMsg("楼层为空");
        }
        if (vo.getArea() == null) {
            return ResultBean.failedResultOfParamWithMsg("面积为空");
        }
        if (vo.getRoom() == null) {
            return ResultBean.failedResultOfParamWithMsg("户型室为空");
        }
        if (vo.getHall() == null) {
            return ResultBean.failedResultOfParamWithMsg("户型厅为空");
        }
        if (vo.getToilet() == null) {
            return ResultBean.failedResultOfParamWithMsg("户型卫为空");
        }
        if (vo.getHouseNature() == null || vo.getHouseNature() == ' ') {
            return ResultBean.failedResultOfParamWithMsg("请选择房屋属性");
        }
        if (vo.getHousePropertyId() == null) {
            return ResultBean.failedResultOfParamWithMsg("请选择小区");
        }
        if (vo.getOrgId() == null || vo.getOwnerName() == null) {
            return ResultBean.failedResultOfParamWithMsg("请选择产权单位");
        }
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

        House house = new House();
        if (StringUtils.isBlank(vo.getId())) {

            //同一个小区中，不能出现同一个楼宇单元的房产
            List<House> list = houseRepository.findByUnitAndRoomNumber(vo.getUnit(), vo.getRoomNumber());
            if (list.size() > 0) {
                return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现相同的房产");
            }

            //不能出现相同的水表号
            if (StringUtils.isNotBlank(vo.getWaterMeter())) {
                List<House> waterMeters = houseRepository.findByWaterMeter(vo.getWaterMeter());
                if (!waterMeters.isEmpty()) {
                    return ResultBean.failedResultOfParamWithMsg("不能出现相同的水表号");
                }
            }

            if (StringUtils.isNotBlank(vo.getElectricMeter())) {
                List<House> electricMeters = houseRepository.findByElectricMeter(vo.getElectricMeter());
                if (!electricMeters.isEmpty()) {
                    return ResultBean.failedResultOfParamWithMsg("不能出现相同的电表号");
                }
            }

            if (StringUtils.isNotBlank(vo.getGasMeter())) {
                List<House> gasMeters = houseRepository.findByGasMeter(vo.getGasMeter());
                if (!gasMeters.isEmpty()) {
                    return ResultBean.failedResultOfParamWithMsg("不能出现相同的燃气表号");
                }
            }

            //获取当省份的3个首字母
            Area area = areaRepository.findByIdAndLevel(vo.getProvinceId(), 1);
            String province = pinyin(area.getName());//省份3个首字母

            //id=省份首字母
            String newId = province + System.currentTimeMillis() + RandomUtils.nextInt(1000, 10000);//省份3位首字母 + 10数字


            house.setId(newId);  //生成id
            house.setStatus(1);
            house.setOperationId(tokenRedisVo.getId());
            house.setCreateTime(new Date());
            //设置房屋配租方式：1单承租人（整租）  2多承租人（合租）  默认为1
            house.setMatchType(1);
            house.setLeaseStatus(0);
        }
        else {
            //查询传的id房屋信息
            Optional<House> optional = houseRepository.findById(vo.getId());
            if (!optional.isPresent()) {
                //空数据
                return ResultBean.failedResultWithMsg("当前数据已不存在！");
            }

            //参数校验
            if ((!optional.get().getRoomNumber().equals(vo.getRoomNumber())) || (!optional.get().getUnit().equals(vo.getUnit()))) {
                List<House> list = houseRepository.findByUnitAndRoomNumber(vo.getUnit(), vo.getRoomNumber());
                if (list.size() > 0) {
                    return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现相同的房产");
                }
            }

            if (StringUtils.isNotBlank(vo.getWaterMeter())) {
                if ((!optional.get().getWaterMeter().equals(vo.getWaterMeter()))) {
                    List<House> waterMeters = houseRepository.findByWaterMeter(vo.getWaterMeter());
                    if (!waterMeters.isEmpty()) {
                        return ResultBean.failedResultOfParamWithMsg("不能出现相同的水表号");
                    }
                }
            }

            if (StringUtils.isNotBlank(vo.getElectricMeter())) {
                if ((!optional.get().getElectricMeter().equals(vo.getElectricMeter()))) {
                    List<House> electricMeters = houseRepository.findByElectricMeter(vo.getElectricMeter());
                    if (!electricMeters.isEmpty()) {
                        return ResultBean.failedResultOfParamWithMsg("不能出现相同的电表号");
                    }
                }
            }

            if (StringUtils.isNotBlank(vo.getGasMeter())) {
                if ((!optional.get().getGasMeter().equals(vo.getGasMeter()))) {
                    List<House> gasMeters = houseRepository.findByGasMeter(vo.getGasMeter());
                    if (!gasMeters.isEmpty()) {
                        return ResultBean.failedResultOfParamWithMsg("不能出现相同的燃气表号");
                    }
                }
            }

            //不同就复制数据
            BeanUtils.copyProperties(optional.get(), house);

            //更新智能锁产权人名称
            List<SmartLock> smartLocks = smartLockRepository.findByHouseId(vo.getId());
            if (!smartLocks.isEmpty()) {
                SmartLock smartLock = smartLocks.get(0);
                smartLock.setCommunityId(vo.getResidenceCommunityId());//小区id
                //根据小区ID获得小区名称
                Optional<Community> community = communityRepository.findById(vo.getResidenceCommunityId());
                if (community.isPresent()) {
                    smartLock.setCommunityName(community.get().getName());//名称
                }
                smartLock.setBuildId(vo.getBuildingNum());//楼栋id

                //根据楼栋ID获得楼栋名称
                Optional<CommunityBuild> communityBuild = communityBuildRepository.findById(vo.getBuildingNum());
                if (communityBuild.isPresent()) {
                    smartLock.setBuildName(communityBuild.get().getName());//楼栋名称
                }
                smartLock.setUnitId(vo.getUnit());//单元id

                //根据单元id获得单元名称

                Optional<CommunityBuildUnit> communityBuildUnit = communityBuildUnitRepository.findById(vo.getUnit());
                if (communityBuildUnit.isPresent()) {
                    smartLock.setUnitName(communityBuildUnit.get().getName());//单元名称
                }
                smartLock.setRoomNumber(vo.getRoomNumber());//房号
                smartLock.setOrgName(vo.getOwnerName());//产权人名称

                smartLockRepository.save(smartLock);
            }
        }

        //产权所有人id ：根据电话号码匹配用户
        User user = userRepository.findByMobile(vo.getOwnerMobile());
        if (user != null) {
            house.setOwnerUserId(user.getId());
        }

        house.setProvinceId(vo.getProvinceId());
        house.setCityId(vo.getCityId());
        house.setDistrictId(vo.getDistrictId());
        house.setStreetId(vo.getStreetId());
        house.setResidenceCommunityId(vo.getResidenceCommunityId());
        house.setDecorateType(vo.getDecorateType());//装修类型
        house.setAddress(vo.getAddress());
        house.setCommunityId(vo.getCommunityId());
        house.setBuildingNum(vo.getBuildingNum());
        house.setUnit(vo.getUnit());
        house.setRoomNumber(vo.getRoomNumber());
        house.setFloor(vo.getFloor());
        house.setArea(vo.getArea());
        house.setRoom(vo.getRoom());
        house.setHall(vo.getHall());
        house.setToilet(vo.getToilet());
        house.setOwnerName(vo.getOwnerName());
        house.setOwnerMobile(vo.getOwnerMobile());
        house.setStartTime(vo.getStartTime());
        house.setWaterMeter(vo.getWaterMeter());
        house.setElectricMeter(vo.getElectricMeter());
        house.setGasMeter(vo.getGasMeter());
        house.setHousePropertyId(vo.getHousePropertyId());
        house.setHouseNature(vo.getHouseNature());
        house.setOrgId(vo.getOrgId());
        house.setOwnerType(vo.getOwnerType());//owner_type '产权归属类型：1产权单位  2个人',
        houseRepository.save(house);

        return ResultBean.successfulResult(null);
    }

    /**
     * 删除物业房产
     *
     * @param accessToken
     * @param id
     * @return 房产有账单的不可删除
     */
    @Transactional
    public ResultBean<Object> deleteCommunityHouse(String accessToken, String id) {
        log.info("AppCommunityService deleteCommunityHouse accessToken={},id={}", accessToken, id);

        Optional<House> optional = houseRepository.findById(id);
        if (!optional.isPresent()) {
            return ResultBean.failedResultWithMsg("房屋不存在");
        }
        List<HouseReturnPremium> byHouseId = houseReturnPremiumRepository.findByHouseId(id);
        if (!byHouseId.isEmpty()) {
            for (HouseReturnPremium houseReturnPremium : byHouseId) {
                Integer paymentStatus = houseReturnPremium.getPaymentStatus();
                if (paymentStatus == 0) {
                    return ResultBean.failedResultWithMsg("有退费记录未处理不能删除房产");
                }
            }
        }
        //有房屋管理员的房产不能删除
        List<CommunityHouseUser> byCommunityHouseIdAndStatusAndType = communityHouseUserRepository.findByCommunityHouseIdAndStatusAndType(id, 1,3);
        if (byCommunityHouseIdAndStatusAndType.size() > 0) {
            return ResultBean.failedResultWithMsg("房产有房屋管理员不能删除");
        }
        //有承租人的房产不能删除
        List<CommunityHouseUser> byCommunityHouseIdAndStatus = communityHouseUserRepository.findByCommunityHouseIdAndStatus(id, 1);
        if (byCommunityHouseIdAndStatus.size() > 0) {
            return ResultBean.failedResultWithMsg("房产有承租人不能删除");
        }
        //物业房产：有账单的不能删除
        List<EstateExpenseBill> estateExpenseBill = estateExpenseBillRepository.findByHouseId(id);
        List<EstateRentBill> estateRentBill = estateRentBillRepository.findByHouseId(id);
        if (estateExpenseBill.size() > 0 || estateRentBill.size() > 0) {
            return ResultBean.failedResultWithMsg("房产有账单不能删除");
        }

        //合租房子存在子房子不给删除
        List<House> houseList = houseRepository.findByHousemateIdAndStatus(id, 1);
        if (CollectionUtil.isNotEmpty(houseList)) {
            return ResultBean.failedResultWithMsg("房产存在子房屋不可删除");
        }

        houseRepository.deleteById(id);
        //删除绑定待办事项
        jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1", id);

        //删除智能锁授权信息
        List<SmartLock> smartLockList = smartLockRepository.findByHouseId(id);
        if (CollectionUtil.isNotEmpty(smartLockList)) {
            Long lockId = smartLockList.get(0).getId();
            //删除智能锁授权信息
            return lockManagerService.deleteLock(accessToken,lockId);
        }
        return ResultBean.successfulResult(null);
    }

    /**
     * 查询小区
     *
     * @param accessToken 用户登录自动查询出名下管理的房屋 如是企业用户登录查询出企业用户名下房产 如是物业查询物业关联的小区
     */
    public ResultBean<List<Map<String, Object>>> findCommunity(String accessToken) {
        try {

            //验证登录
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                //未登录提示异常 40001 --- accessToken失效
                return ResultBean.failedResultOfToken();
            }
            //String userId = tokenRedisVo.getId();
            //获取登陆状态
            Integer loginType = tokenRedisVo.getLoginType();
            //企业id
            Long enterpriseId = tokenRedisVo.getEnterpriseId();

            List<Map<String, Object>> list = new ArrayList<>();

            //1企业用户登录 2普通用户登录
            if (loginType == 1) {
                //判断用户所属机构是否设置层级架构
                DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
                if (hierarchy == null) {
                    return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
                }
                //查询出企业的标签
                Long id = hierarchy.getId();
                List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
                List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

                //1、政府机构，根据区域标签所对应区域id查所有小区
                Long areaTagId = hierarchy.getTagId();//区域标签
                Long areaId = null;
                if (enterpriseTagIds.contains(3L)) {
                    areaId = hierarchy.getAreaId();//区域id
                }
                String areaTag = "";
                if (areaTagId == 1) {
                    areaTag = "province_id";
                } else if (areaTagId == 2) {
                    areaTag = "city_id";
                } else if (areaTagId == 3) {
                    areaTag = "district_id";
                } else if (areaTagId == 4) {
                    areaTag = "street_id";
                } else if (areaTagId == 5) {
                    areaTag = "community_id";
                }

                //2、产权单位，根据orgId查公租房所对应小区
                List<String> rcIds = new ArrayList<>();
                if (enterpriseTagIds.contains(2L)) {
                    List<House> houses = houseRepository.findByOrgId(enterpriseId);
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }

                //3、物业，查已绑定该物业的小区
                Long hierarchyOrgId = null;
                if (enterpriseTagIds.contains(4L)) {
                    hierarchyOrgId = hierarchy.getOrgId();
                }

                //4、单位承租人，查已绑定该物业的小区
                if (enterpriseTagIds.contains(7L)) {
                    List<House> houses = houseRepository.findByUnitLesseeIdAndStatus(enterpriseId,1);
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }
                //5、房东，查已绑定该物业的小区
                if (enterpriseTagIds.contains(8L)) {
                    List<House> houses = houseRepository.findByOrgIdAndStatus(enterpriseId,1);
                    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                }
                StringBuilder where = new StringBuilder();
                List<Object> args = new ArrayList<>();
                where.append(" where 1 = 1");
                where.append(" and (c." + areaTag + " = ? or c.org_id = ? or c.id in ");
                args.add(areaId);
                args.add(hierarchyOrgId);
                if (rcIds != null && !rcIds.isEmpty()) {
                    where.append(SqlUtils.foreachIn(rcIds.size()));
                    args.addAll(rcIds);
                } else {
                    where.append("(null)");
                }
                where.append(")");

                list = jdbcTemplate.queryForList("SELECT c.id AS communityId,c.name AS communityName,c.address FROM `saas_community` c"
                        + where + " AND c.status = 1", args.toArray());
            }

            if (loginType == 2) {
                // 普通用户登录只看自己房屋数据
                List<String> houseIdList = communityService.getHouseIdList(tokenRedisVo.getId());
                // 根据房屋id查出对应的房产
                if (!houseIdList.isEmpty()) {
                    String sql = "SELECT c.id AS communityId,c.name AS communityName,c.address \n" +
                            "FROM saas_house h LEFT JOIN `saas_community` c ON h.residence_community_id = c.id\n" +
                            "WHERE h.id IN " + SqlUtils.foreachIn(houseIdList.size());
                    list = jdbcTemplate.queryForList(sql, houseIdList.toArray());
                    //去重
                    list = list.stream().distinct().collect(Collectors.toList());

                }
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<HouseInfoVo>> findCommunitys(String accessToken, Integer scope, Long orgId, String communityId, String buildingNum, String unitId, String roomNumber, String companyName) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }

            Long enterpriseId = orgId;
            DepartmentHierarchy hierarchy = null;
            if (orgId != null) {
                hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
                if (hierarchy == null) {
                    return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
                }
            }
            List<HouseInfoVo> list = new ArrayList<>();
            List<Object> args = new ArrayList<>();
            String sql = null;
            if (scope == 6) {
                sql = " select h.id,\n" +
                        "co.id as communityId ,   \n" +
                        "co.name AS communityName,   \n" +
                        "bu.id as buildingNum ,   \n" +
                        "bu.name AS buildingName,   \n" +
                        "bun.id as unit ,    \n" +
                        "bun.name as unitName,   \n" +
                        "h.room_number as roomNumber, \n" +
                        "h.house_nature as houseNature,  \n" +
                        "CONCAT(date_format( a.start_date, '%Y-%m-%d') , '至',date_format( a.end_date, '%Y-%m-%d') ) as term, \n" +
                        "a.charge_mode as payType \n" +
                        "from  saas_house h  \n" +
                        "right join (select * from  m_match_user_house where company_name=? ) a on a.house_id=h.id\n" +
                        "LEFT JOIN saas_community co on co.id=h.residence_community_id  \n" +
                        "LEFT JOIN saas_community_build bu on bu.id=h.building_num  \n" +
                        "LEFT JOIN saas_community_build_unit bun on bun.id=h.unit where 1=1 ";

                StringBuilder where = new StringBuilder();
                //小区id
                if (StringUtils.isNotBlank(communityId)) {
                    where.append(" and h.residence_community_id = '" + communityId + "'");
                }

                //楼栋
                if (StringUtils.isNotBlank(buildingNum)) {
                    where.append(" and h.building_num = " + buildingNum);
                }

                //单元
                if (StringUtils.isNotBlank(unitId)) {
                    where.append(" and h.unit = " + unitId);
                }

                //房号
                if (StringUtils.isNotBlank(roomNumber)) {
                    where.append(" and h.room_number = '" + roomNumber + "'");
                }

                args.add(companyName);
                list = jdbcTemplate.query(sql + where.toString(), new BeanPropertyRowMapper<>(HouseInfoVo.class), args.toArray());
            } else if (scope == 7 || scope == 8) {
                sql = "SELECT  \n" +
                        "h.id, \n" +
                        "c.id as communityId, \n" +
                        "c.name communityName,  \n" +
                        "b.id as  buildingNum,  \n" +
                        "b.name buildingName,  \n" +
                        "u.id as unit, \n" +
                        "u.name  as unitName, \n" +
                        "h.house_nature as houseNature,  \n" +
                        "h.room_number as roomNumber  ,\n" +
                        "IFNULL(\t( select concat (date_format( co.start_time, '%Y-%m-%d') ,'至', date_format( co.ent_time, '%Y-%m-%d') ) from  saas_house_contract co where  co.status <>0 and  co.house_id = h.id GROUP BY co.operation_time DESC LIMIT 1 ) ,\t\n" +
                        "( select  concat (date_format( ho.start_date, '%Y-%m-%d') ,'至', date_format( ho.end_date, '%Y-%m-%d') ) from  m_match_user_house ho\n" +
                        "where  ho.house_id=h.id) )as term,\n" +
                        "IFNULL( ( select co.pay_type from  saas_house_contract co where  co.status <>0 and  co.house_id = h.id GROUP BY co.operation_time DESC LIMIT 1 ),  \t\n" +
                        "( select  ho.charge_mode from  m_match_user_house ho\n" +
                        "where  ho.house_id=h.id))as payType\n" +
                        " FROM saas_house h      \n" +
                        " LEFT JOIN saas_community c ON c.id=h.residence_community_id      \n" +
                        " LEFT JOIN saas_community_build b ON b.id=h.building_num      \n" +
                        " LEFT JOIN saas_community_build_unit u ON u.id=h.unit  ";

                //2、产权单位，根据orgId查公租房所对应小区
                List<String> rcIds = new ArrayList<>();
                //if (scope == 7) {
                //    List<House> houses = houseRepository.findByOrgId(enterpriseId);
                //    rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
                //}

                //3、物业，查已绑定该物业的小区  //加了 || scope == 7
                Long hierarchyOrgId = null;
                if (scope == 8 || scope == 7) {
                    hierarchyOrgId = hierarchy.getOrgId();
                }

                StringBuilder where = new StringBuilder();
                where.append(" where 1 = 1");
                //where.append(" and ( c.org_id = ? or c.id in ");
                where.append(" and ( h.org_id = ? or h.house_property_id = ? ) ");
                args.add(hierarchyOrgId);
                args.add(hierarchyOrgId);
                //if (rcIds != null && !rcIds.isEmpty()) {
                //    where.append(SqlUtils.foreachIn(rcIds.size()));
                //    args.addAll(rcIds);
                //} else {
                //    where.append("(null)");
                //}
                //where.append(")");


                //小区id
                if (StringUtils.isNotBlank(communityId)) {
                    where.append(" and h.residence_community_id = '" + communityId + "'");
                }

                //楼栋
                if (StringUtils.isNotBlank(buildingNum)) {
                    where.append(" and h.building_num = " + buildingNum);
                }

                //单元
                if (StringUtils.isNotBlank(unitId)) {
                    where.append(" and h.unit = " + unitId);
                }

                //房号
                if (StringUtils.isNotBlank(roomNumber)) {
                    where.append(" and h.room_number = '" + roomNumber + "'");
                }
                list = jdbcTemplate.query(sql + where + " AND c.status = 1 AND h.house_nature != 7 ", new BeanPropertyRowMapper<>(HouseInfoVo.class), args.toArray());
                // log.info(sql + where + " AND c.status = 1" + args.toArray());
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }


    /**
     * 查询楼栋
     * @param communityId 小区id
     * @return 小区内所有楼宇
     * 查询本小区所有楼宇
     */
    public ResultBean<List<Map<String, Object>>> findBuild(String communityId) {
        try {
            //校验参数
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }

            //查询本小区所有楼宇
            List<CommunityBuild> builds = communityBuildRepository.findByCommunityId(communityId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuild build : builds) {
                Map<String, Object> map = new HashMap<>();
                map.put("buildId", build.getId());
                map.put("buildName", build.getName());
                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 查询单元
     * @param communityId 小区id
     * @param buildId 楼栋id
     * @return 小区内所有单元
     * 查询本小区所有楼宇中的单元
     */
    public ResultBean<List<Map<String, Object>>> findUnit(String communityId, Long buildId) {
        try {
            //校验参数
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }

            //查询本小区所有楼宇的的单元
            List<CommunityBuildUnit> units = communityBuildUnitRepository.findByCommunityIdAndBuildId(communityId, buildId);
            List<Map<String, Object>> list = new ArrayList<>();
            for (CommunityBuildUnit unit : units) {
                Map<String, Object> map = new HashMap<>();
                map.put("unitId", unit.getId());
                map.put("unitName", unit.getName());
                list.add(map);
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 查询房号[公租房+物业房产]
     * @param communityId 小区id
     * @param buildId 楼栋id
     * @param unitId 单元id
     * @return 查询单元内所有房型的房号
     */
    public ResultBean<List<String>> findRoomNumberByCommunityHouse(String communityId, Long buildId, Long unitId) {
        try {
            if (StringUtils.isBlank(communityId)) {
                return ResultBean.failedResultOfParamWithMsg("小区id为空");
            }
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }
            if (unitId == null) {
                return ResultBean.failedResultOfParamWithMsg("单元id为空");
            }

            // 性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
            //查询单元内所有房型的房号
            List<Integer> houseNatureList = new ArrayList<>();
            List<HouseType> all = houseTypeRepository.findAll();
            all.forEach( a -> {
                houseNatureList.add(Math.toIntExact(a.getId()));
            });
            //houseNatureList.add(1);
            //houseNatureList.add(2);
            //houseNatureList.add(3);
            //houseNatureList.add(4);
            //houseNatureList.add(5);
            List<House> houseList = houseRepository.findByResidenceCommunityIdAndBuildingNumAndUnitAndHouseNatureIn(communityId, buildId, unitId, houseNatureList);
            List<String> list = new ArrayList<>();
            for (House house : houseList) {
                list.add(house.getRoomNumber());
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 小区总体详情
     * 根据不同的角色登录，查询不同的管辖小区信息 包含产权单位、物业
     */
    public ResultBean<Map<String, Object>> communityTotalDetails(String accessToken) {
        try {
            //获取登录用户信息
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                //未登录提示异常 40001 --- accessToken失效
                return ResultBean.failedResultOfToken();
            }

            //判断用户所属机构是否设置层级架构
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            //根据机构层级关系id查出企业与标签关系数据
            Long id = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            //去重后的用户企业标签
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

            //1、政府机构，根据区域标签所对应区域id查所有小区
            Long areaTagId = hierarchy.getTagId();//区域标签
            Long areaId = null;
            if (enterpriseTagIds.contains(3L)) {
                areaId = hierarchy.getAreaId();//区域id
            }
            String areaTag = "";
            if (areaTagId == 1) {
                areaTag = "province_id";
            } else if (areaTagId == 2) {
                areaTag = "city_id";
            } else if (areaTagId == 3) {
                areaTag = "district_id";
            } else if (areaTagId == 4) {
                areaTag = "street_id";
            } else if (areaTagId == 5) {
                areaTag = "community_id";
            }

            //2、产权单位，根据orgId查公租房所对应小区
            List<String> rcIds = new ArrayList<>();
            if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
                //根据产权单位id查询对应的房屋
                List<House> houses = houseRepository.findByOrgId(enterpriseId);
                rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
            }

            //3、物业，查已绑定该物业的小区
            /*Long hierarchyOrgId = null;
            if (enterpriseTagIds.contains(4L)) {
                hierarchyOrgId = hierarchy.getOrgId();
            }*/

            //物业机构id
            List<Long> hierarchyOrgId = new ArrayList<>();
            //当前登录用户自己所在企业以及下级企业是物业的
            List<OrgVO> org = propertyList(accessToken);
            hierarchyOrgId = org.stream().map(OrgVO::getOrgId).collect(Collectors.toList());

            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");
            where.append(" and (c." + areaTag + " = ? or c.org_id in  ");
            args.add(areaId);
            //名下房屋是否由物业公司管理 有则使用物业公司id查询
            if (hierarchyOrgId != null && !hierarchyOrgId.isEmpty()) {
                where.append(SqlUtils.foreachIn(hierarchyOrgId.size()));
                args.addAll(hierarchyOrgId);
            } else {
                where.append("(?)");
                args.add(null);
            }

            //拼接产权单位名下房屋所属小区id
            where.append("  or c.id in  ");
            if (rcIds != null && !rcIds.isEmpty()) {
                where.append(SqlUtils.foreachIn(rcIds.size()));
                where.append(")");
                args.addAll(rcIds);
            } else {
                where.append("(?))");
                args.add(null);
            }
            where.append("  AND c.status = 1 ");
            //根据条件查询出对应的小区
            List<CommunityVO> data = jdbcTemplate.query("SELECT c.* FROM `saas_community` c" + where + " AND c.status = 1",
                    new BeanPropertyRowMapper<>(CommunityVO.class), args.toArray());

            Map<String, Object> map = new HashMap<>();
            //小区总数量
            map.put("communitySum", data.size());

            //小区总用地面积(万平方米)
            Double communityTotalLandAreas = 0d;
            Optional<Double> communityTotalLandAreasOptional = data.stream().filter(vo -> vo.getCommunityTotalLandArea() != null).map(CommunityVO::getCommunityTotalLandArea).reduce(Double::sum);
            if (communityTotalLandAreasOptional.isPresent()) {
                communityTotalLandAreas = communityTotalLandAreasOptional.get();
            }
            map.put("communityTotalLandAreas", communityTotalLandAreas);

            //小区占地面积(万平方米)
            Double communityAreas = 0d;
            Optional<Double> communityAreaOptional = data.stream().filter(vo -> vo.getCommunityArea() != null).map(CommunityVO::getCommunityArea).reduce(Double::sum);
            if (communityAreaOptional.isPresent()) {
                communityAreas = communityAreaOptional.get();
            }
            map.put("communityAreas", communityAreas);

            List<String> idList = data.stream().map(CommunityVO::getId).collect(Collectors.toList());
            //楼房总栋数
            List<CommunityBuild> buildTotal = communityBuildRepository.findByCommunityIdIn(idList);
            map.put("buildTotal", buildTotal.size());
            //层数
            long houseNumberTotal = 0;
            for (CommunityBuild b : buildTotal) {
                Integer houseNumber = b.getHouseNumber();
                houseNumberTotal += houseNumber;
            }
            map.put("houseNumberTotal", houseNumberTotal);

            //单元总数
            List<Long> buildList = buildTotal.stream().map(CommunityBuild::getId).collect(Collectors.toList());
            List<CommunityBuildUnit> unitTotal = communityBuildUnitRepository.findByCommunityIdInAndBuildIdIn(idList, buildList);
            map.put("unitTotal", unitTotal.size());

            //总人口数
            Integer populationNumberTotal = 0;
            Optional<Integer> populationNumberOptional = data.stream().filter(vo -> vo.getPopulationNumber() != null).map(CommunityVO::getPopulationNumber).reduce(Integer::sum);
            if (populationNumberOptional.isPresent()) {
                populationNumberTotal = populationNumberOptional.get();
            }

            map.put("populationNumberTotal", populationNumberTotal);

            //机动车位和非机动车位
            Integer motorVehicles = 0;
            Integer nonMotorVehicles = 0;
            List<CommunityPublicFacilities> communityPublicFacilitiesList = communityPublicFacilitiesRepository.findByCommunityIdIn(idList);
            Optional<Integer> motorVehiclesOptional = communityPublicFacilitiesList.stream().filter(vo -> vo.getMotorVehicles() != null).map(CommunityPublicFacilities::getMotorVehicles).reduce(Integer::sum);
            if (motorVehiclesOptional.isPresent()) {
                motorVehicles = motorVehiclesOptional.get();
            }
            map.put("motorVehicles", motorVehicles);

            //非机动车位
            Optional<Integer> nonMotorVehiclesOptional = communityPublicFacilitiesList.stream().filter(vo -> vo.getNonMotorVehicles() != null).map(CommunityPublicFacilities::getNonMotorVehicles).reduce(Integer::sum);
            if (nonMotorVehiclesOptional.isPresent()) {
                nonMotorVehicles = nonMotorVehiclesOptional.get();
            }
            map.put("nonMotorVehicles", nonMotorVehicles);

            // apply_status '申请状态：0草稿、1未审核 2审批中 3已审核 4审核不通过 6已撤销 9已过期'
            //总户数：公租房资格申请审批通过的户数，以承租人为依据统计
            StringBuilder residenceCommunityIdStr = new StringBuilder();
            for (String residenceCommunityId : idList) {
                residenceCommunityIdStr.append("'" + residenceCommunityId + "'").append(",");
            }
            String residenceCommunityId = null;
            if (residenceCommunityIdStr.length() > 0) {
                residenceCommunityId = residenceCommunityIdStr.toString().substring(0, residenceCommunityIdStr.toString().lastIndexOf(","));
            }
            //populationNumberTotal: 11  houseNum: 1
            //Long houseNum1 = jdbcTemplate.queryForObject(" select COUNT(1) from saas_house_eligible_apply a left join saas_house_match_user u on a.id=u.apply_id left join saas_house h on h.id=u.house_id where a.apply_status=3  "
              //      + " and h.residence_community_id in ( " + residenceCommunityId + ") ", Long.class);
            //总户数;非公租房
            //Long houseNum2 = jdbcTemplate.queryForObject(" SELECT COUNT(1) as count FROM m_match_user_house \n", Long.class);
            //Long houseNum = houseNum1 + houseNum2;
            Long houseNum = jdbcTemplate.queryForObject(" SELECT COUNT(1) as count FROM saas_house h where h.org_id = ? OR h.unit_lessee_id = ? OR h.house_property_id = ?\n", Long.class,enterpriseId,enterpriseId,enterpriseId);
            map.put("houseNum", houseNum); //户数

            return ResultBean.successfulResult(map);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    //当前登录用户自己所在企业以及下级企业是物业的房产
    public List<OrgVO> propertyList(String accessToken) {

        //从redis中取出登录信息
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);

        //企业id
        Long enterpriseId = tokenRedisVo.getEnterpriseId();

        //根据企业id查出机构层级关系
        DepartmentHierarchy departmentHierarchy = departmentHierarchyRepository.findByOrgId(enterpriseId);
        //查找当前机构层级节点所有节点(不包含当前节点)
        List<Long> allChildHierarchy = allChildByHierarchy(departmentHierarchy.getId());
        //当前机构所有层级节点的id查出机构层级关系
        List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByIdIn(allChildHierarchy);
        //加入当前节点公司
        departmentHierarchyList.add(departmentHierarchy);
        //物业公司集合
        List<OrgVO> propertyList = new ArrayList<>();

        for (DepartmentHierarchy dh : departmentHierarchyList) {
            //查询出每个机构所属标签关系
            List<DepartmentEnterpriseTagRef> entityList = departmentEnterpriseTagRefRepository.findByDepRefId(dh.getId());
            //获取机构标签
            List<Long> tagIdList = entityList.stream().map(DepartmentEnterpriseTagRef::getTagId).collect(Collectors.toList());

            //当前是物业公司
            if (tagIdList.contains(4L)) {    //物业公司
                OrgVO orgVO = new OrgVO();
                orgVO.setOrgId(dh.getOrgId());
                orgVO.setOrgName(dh.getName());
                propertyList.add(orgVO);
            }
        }

        return propertyList;

    }

    /**
     * 获取省份3个首字母
     *
     * @param name
     * @return
     */
    public String pinyin(String name) {
        StringBuffer pybf = new StringBuffer();
        char[] arr = name.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < arr.length; i++) {
            if (Character.toString(arr[i]).matches("[\\u4E00-\\u9FA5]+")) {
                try {
                    String[] temp = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
                    if (temp != null) {
                        pybf.append(temp[0].charAt(0));
                    }
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pybf.append(arr[i]);
            }
        }
        return pybf.toString().replaceAll("\\W", "").trim().substring(0, 3);
    }

    //查找当前机构层级节点所有节点(不包含当前节点)saas_department_hierarchy
    public List<Long> allChildByHierarchy(Long id) {
        String sql = " select id from ( select t1.id,if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', id),0) as ischild from ( select id,parent_id from `saas_department_hierarchy` t order by parent_id, id ) t1,(select @pids := ?) t2 ) t3 where ischild != 0 \n";
        return jdbcTemplate.queryForList(sql, new Object[]{id}, Long.class);
    }


    /**
     * 查询小区管理信息
     * @param accessToken
     * @param id 小区管理信息表id
     * @return
     */
    public ResultBean<List<CommunityVO>> findCommunityInfo(String accessToken, String id) {
        try {
            //获取登录用户信息
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                //未登录提示异常 40001 --- accessToken失效
                return ResultBean.failedResultOfToken();
            }
            //查询出对应小区的省、市、区、镇、社区等信息
            String SQL = " SELECT c.*,   " +
                    "a1.name streetName,a2.name communityName, a3.name districtName,a4.name cityName ,a5.name provinceName    " +
                    "FROM saas_community c " +
                    "LEFT JOIN sys_area a1 ON c.street_id=a1.id " +
                    "LEFT JOIN sys_area a2 ON c.community_id=a2.id  " +
                    "LEFT JOIN sys_area a3 ON c.district_id=a3.id  " +
                    "LEFT JOIN sys_area a4 ON c.city_id=a4.id  " +
                    "LEFT JOIN sys_area a5 ON c.province_id=a5.id  ";
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where c.`status` = 1 AND c.id = ? ");
            args.add(id);
            List<CommunityVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(CommunityVO.class), args.toArray());
            return ResultBean.successfulResult(data);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }

    }
    /**
     * 查询出对应区域下的所以产权单位/个人信息
     * @param accessToken
     * @param ownerType 产权归属类型：1产权单位  2个人
     * @return
     */
    public ResultBean<List<OrgVO>> AllProperty(String accessToken, Integer ownerType) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据企业ID查询出对应物业
            Optional<Department> optional = departmentRepository.findById(enterpriseId);
            if (!optional.isPresent()) {
                //如果不存在
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取物业数据
            Department department = optional.get();
            //获取省份ID
            Long provinceId = department.getProvinceId();//省id
            //根据省份ID,产权ID,查询出对应的产权单位
            // String sql = "SELECT h.`org_id` AS orgId,h.`name` AS orgName FROM `saas_department_hierarchy` h LEFT JOIN `saas_department_enterprise_tag_ref` r ON h.id = r.dep_ref_id  \n" +
            //         "WHERE h.`org_id` IN (SELECT d.id FROM `saas_department` d WHERE d.`province_id` = ? AND d.`parent_id` = 0) \n" +
            //         "AND r.`tag_id` = 4";
            String sql = "SELECT \n" +
                    " DISTINCT h.`org_id` AS orgId,\n" +
                    "  h.`name` AS orgName ,\n" +
                    "  u.`mobile` AS orgMobile\n" +
                    "FROM\n" +
                    "  `saas_department_hierarchy` h \n" +
                    "  LEFT JOIN `saas_department_enterprise_tag_ref` r \n" +
                    "    ON h.id = r.dep_ref_id \n" +
                    "    LEFT JOIN `saas_department` d\n" +
                    "    ON d.id = h.`org_id`\n" +
                    "    LEFT JOIN `saas_user_department_ref` udr \n" +
                    "    ON h.`org_id` = udr.dep_id \n" +
                    "    LEFT JOIN `saas_user` u\n" +
                    "    ON u.id = udr.user_id AND u.`type` = 1 \n" +
                    "    WHERE h.`org_id` IN \n" +
                    "  (SELECT \n" +
                    "    d.id \n" +
                    "  FROM\n" +
                    "    `saas_department` d \n" +
                    "  WHERE d.`province_id` = ? \n" +
                    "    AND d.`parent_id` = 0) \n" +
                    "    AND d.`owner_type` = ?";
            //创建一个字符串拼接变量
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            args.add(provinceId);
            args.add(ownerType);
            if (ownerType == 1 ) {
                where.append(" AND r.`tag_id` = 2");
            } else {
                where.append(" AND r.`tag_id` = 8");
            }
            //查询出对应物业的数据
            List<OrgVO> list = jdbcTemplate.query(sql + where, new BeanPropertyRowMapper<>(OrgVO.class), args.toArray());
            //把没有手机号的删除
            for (int i = list.size() - 1; i >= 0; i--) {
                String orgMobile = list.get(i).getOrgMobile();
                if (orgMobile == null) {
                    list.remove(i);
                }
            }
            //把数据返回给前端页面
            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 查询机构下对应区域的小区列表 20230224
     * @param accessToken
     */
    public ResultBean<List<CommunityStrVO>> findAreaCommunityList(String accessToken) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            Long areaId = hierarchy.getAreaId();
            String sql = "SELECT distinct c.address, c.id AS communityId, c.`name` AS communityName  FROM `saas_department_hierarchy` dh\n" +
                    "left join saas_community c on " +
                    "(dh.area_id = c.province_id or " +
                    "dh.area_id = c.city_id or " +
                    "dh.area_id = c.district_id or " +
                    "dh.area_id = c.street_id or " +
                    "dh.area_id = c.community_id ) WHERE dh.area_id = ? ";
            List<CommunityStrVO> data = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CommunityStrVO.class), areaId);
            return ResultBean.successfulResult(data);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultBean.failedResultOfException();
        }
    }

    /**20230306 xiaolong
     * 查询单个房产信息
     * @param accessToken
     * @param houseId 房产id
     * @return
     */
    public ResultBean<List<HouseVO>> findCommunityHouse(String accessToken, String houseId) {
        log.info("AppCommunityService findCommunityHouseList accessToken={},id={}", accessToken, houseId);

        try {
            if (houseId == null) {
                return ResultBean.failedResultOfParamWithMsg("请选择房屋");
            }

            String SQL = " SELECT h.*,b.name buildingNumName,u.name unitName," +
                    "           a1.name province,a2.name city,a3.name district,a4.name street,a5.name community   " +
                    "          FROM  saas_house h " +
                    "          LEFT JOIN saas_community_build b ON b.id=h.building_num  " +
                    "          LEFT JOIN saas_community_build_unit u ON h.unit=u.id  " +
                    "          LEFT JOIN sys_area a1 ON h.province_id=a1.id  " +
                    "          LEFT JOIN sys_area a2 ON h.city_id=a2.id  " +
                    "          LEFT JOIN sys_area a3 ON h.district_id=a3.id  " +
                    "          LEFT JOIN sys_area a4 ON h.street_id=a4.id  " +
                    "          LEFT JOIN sys_area a5 ON h.community_id=a5.id    ";

            //StringBuilder where = new StringBuilder("WHERE 1 = 1 AND h.housemate_id is null \n");
            StringBuilder where = new StringBuilder("WHERE 1 = 1 \n");
            List<Object> args = new ArrayList<>();

            where.append(" AND h.id = ? \n");
            args.add(houseId);

            //查询单元名称、楼宇名称、房产、街道层级
            List<HouseVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());

            //查询物业名称
            List<Long> housePropertyIdList = data.stream().map(HouseVO::getHousePropertyId).collect(Collectors.toList());
            List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByOrgIdIn(housePropertyIdList);
            //查询出房屋属性；
            Iterable<HouseType> all = houseTypeRepository.findAll();
            for (HouseVO houseVO : data) {
                for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
                    if (houseVO.getHousePropertyId().equals(departmentHierarchy.getOrgId())) {
                        //设置对应物业公司名
                        houseVO.setHouseProperty(departmentHierarchy.getName());
                    }
                }
                //    新增房屋性质 20230307 xiaolong
                    Integer houseNature = houseVO.getHouseNature();
                    for (HouseType houseType : all) {
                        Integer id = Math.toIntExact(houseType.getId());
                        if (houseNature == id) {
                            houseVO.setHouseNatureName(houseType.getType());
                        }
                    }
            //        查询对应的产权手机号
            //    Long orgId = houseVO.getOrgId();
            //    List<UserDepartmentRef> byDepId = userDepartmentRefRepository.findByDepId(orgId);
            //    if (!byDepId.isEmpty()) {
            //        String userId = byDepId.get(0).getUserId();
            //        User byId = userRepository.findById(userId);
            //        String mobile = byId.getMobile();
            //        houseVO.setOwnerMobile(mobile);
            //    }
            //  新增建设小区时间
                Optional<Community> community = communityRepository.findById(houseVO.getResidenceCommunityId());
                houseVO.setBuildTime(community.get().getBuildTime());
            }

            //查询小区名称
            List<String> rcIds = data.stream().filter(h -> h.getResidenceCommunityId() != null).map(HouseVO::getResidenceCommunityId).distinct().collect(Collectors.toList());
            if (!rcIds.isEmpty()) {
                Map<String, String> map = communityRepository.findByIdIn(rcIds).stream().collect(Collectors.toMap(Community::getId, Community::getName));
                if (!map.isEmpty()) {
                    //设置对应小区名
                    data.forEach(h -> h.setResidenceCommunityName(map.get(data.get(0).getResidenceCommunityId())));
                }
            }


            return ResultBean.successfulResult(data);

        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.failedResultOfException();
        }

    }
}
